package db.derby;

import java.sql.*;
import java.text.*;
import java.util.Date;
import java.util.Properties;

public class SingleTonDBWrapper {
	private int counter = 0;
	private static SingleTonDBWrapper _instance;
	private Connection conn = null;

	private SingleTonDBWrapper() {
		initDB();
		new AutoPurge(5).start();
	}

	public static synchronized SingleTonDBWrapper getInstance() {
		if (_instance == null) {
			_instance = new SingleTonDBWrapper();
		}
		return _instance;
	}

	/**
	 * 
	 * @param conTst
	 * @return true if table exists, false if not
	 * @throws SQLException
	 */
	public static boolean wwdChk4Table(Connection conTst, String table)
			throws SQLException {
		boolean chk = true;
		boolean doCreate = false;
		try {
			Statement s = conTst.createStatement();
			s.execute("select * from " + table + " where 1=0");
		} catch (SQLException sqle) {
			String theError = (sqle).getSQLState();
			// System.out.println("  Utils GOT:  " + theError);
			/** If table exists will get - WARNING 02000: No row was found **/
			if (theError.equals("42X05")) // Table does not exist
			{
				return false;
			} else if (theError.equals("42X14") || theError.equals("42821")) {
				System.out
						.println("WwdChk4Table: Incorrect table definition. Drop table WISH_LIST and rerun this program");
				throw sqle;
			} else {
				System.out.println("WwdChk4Table: Unhandled SQLException");
				throw sqle;
			}
		}
		// System.out.println("Just got the warning - table exists OK ");
		return true;
	}

	/*** END wwdInitTable **/

	/**
	 * Periodically purge outdated data sets
	 * 
	 */
	class AutoPurge extends Thread {
		SingleTonDBWrapper db;
		int period;

		/**
		 * Constructor
		 * 
		 * @param sec
		 *            Purge period
		 */
		AutoPurge(int sec) {
			this.period = sec;
		}

		public void run() {
			db = SingleTonDBWrapper.getInstance();
			Date dt;
			SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
			while (true) {
				try {
					dt = new Date();
					sleep(this.period * 1000);
					db
							.execute("delete from pos_vel where entry_date<timestamp('"
									+ df.format(dt) + "')");
				} catch (InterruptedException e) {
				}
			}
		}
	}

	public void initDB()
	{
		// ## DEFINE VARIABLES SECTION ##
		// define the driver to use
		//String driver = "org.apache.derby.jdbc.EmbeddedDriver";
		 String driver = "org.apache.derby.jdbc.ClientDriver";
	
		// the database name
		String dbName = "jdbcDemoDB";
		// define the Derby connection URL to use

		// Derby Network Client JDBC driver, by default the database is created
		// in the directory where the Network Server was started up,i.e.DERBY_INSTALL/bin.
		
		try{
			
		 String connectionURL = "jdbc:derby://localhost:1527/" + dbName + ";create=true";
		//String connectionURL = "jdbc:derby:" + dbName + ";create=true";
			 
			// Load Derby Network Client driver class
			Class.forName(driver);
			// Set user and password properties
			Properties properties = new Properties();
			properties.put("user", "APP");
			properties.put("password", "APP");
			// Get a connection
			conn = DriverManager.getConnection(connectionURL, properties);
			conn.setAutoCommit(true);
			System.out.println("Connected to database " + dbName);

		} catch (java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
			System.out
					.println("\n    >>> Please check your CLASSPATH variable   <<<\n");
		}catch(Throwable e){
			
		}
			
		

		Statement s;
		PreparedStatement psInsert;
		// fid is vehicle ID, fid and mid are just added
		String printLine = "_____________________________________________________";
		String createString = "CREATE TABLE pos_vel  "
				+ "(ID INT NOT NULL GENERATED ALWAYS AS IDENTITY "
				+ "   CONSTRAINT pv_PK PRIMARY KEY, "
				+ " ENTRY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
				+ " fid varchar(50) NOT NULL," + " username varchar(50),"
				+ " lat double," + " lon double," + " alt double,"
				+ " vel decimal(10,2)," + " mid varchar(50),"
				+ " heading double" + ") ";

		// Beginning of JDBC code sections
		// ## LOAD DRIVER SECTION ##
		/*try {
			/*
			 * Load the Derby driver. When the embedded Driver is used this
			 * action start the Derby engine. Catch an error and suggest a
			 * CLASSPATH problem
			 */
		/*	Class.forName(driver);
			System.out.println(driver + " loaded. ");
		  } catch (java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
			System.out
					.println("\n    >>> Please check your CLASSPATH variable   <<<\n");
		}
		*/
		// Beginning of Primary DB access section
		// ## BOOT DATABASE SECTION ##
		try {
			// Create (if needed) and connect to the database
			//conn = DriverManager.getConnection(connectionURL);
			//conn.setAutoCommit(true);
			//System.out.println("Connected to database " + dbName);

			// ## INITIAL SQL SECTION ##
			// Create a statement to issue simple commands.

			s = conn.createStatement();
			try {
				// Call utility method to check if table exists.
				// Create the table if needed
				s.execute("drop table pos_vel");
			} catch (Throwable e) {

			}
			// if (!wwdChk4Table(conn,"pos_vel"))
			{
				System.out.println(" . . . . creating table pos_vel");
				s.execute(createString);
			}
		} catch (Throwable e) {
			/*
			 * Catch all exceptions and pass them to the exception reporting
			 * method
			 */
			System.out.println(" . . . exception thrown:");
			errorPrint(e);
		}
		System.out.println("Getting Started With Derby JDBC program ending.");
	}

	/**
	 * 
	 * @param fid
	 * @param user
	 * @param lat
	 * @param lon
	 * @param alt
	 * @param vel
	 * @param mid
	 * @param heading
	 */
	public void insertData(String fid, String user, float lat, float lon,
			float alt, float vel, String mid, float heading) {

		// define the Derby connection URL to use
		// fid und mid(modellId) are just added.
		Statement s;
		String insertString = "insert into pos_vel (fid,username,lat,lon,alt,vel,mid,heading) values ("
				+ "'"
				+ fid
				+ "',"
				+ "'"
				+ user
				+ "',"
				+ lat
				+ ","
				+ lon
				+ ","
				+ alt + "," + vel + "," + "'" + mid + "'," + heading + ")";
		// Beginning of Primary DB access section
		// ## BOOT DATABASE SECTION ##
		// System.out.println(insertString);
		execute(insertString);
	}

	/**
	 * Execute a simple SQL code, no feed back.
	 * 
	 * @param command
	 *            SQL code
	 */
	public void execute(String command) {
		Statement s;
		// Beginning of Primary DB access section
		// ## BOOT DATABASE SECTION ##
		try {
			// check the connection. reconnect the db if connection not valid
			if (!conn.isValid(2))
				this.initDB();
			// ## INITIAL SQL SECTION ##
			// Create a statement to issue simple commands.
			s = conn.createStatement();
			// Call utility method to check if table exists.
			// Create the table if needed
			{
				// System.out.println (command);
				s.execute(command);
			}
		} catch (Throwable e) {
			/*
			 * Catch all exceptions and pass them to the exception reporting
			 * method
			 */
			System.out.println(" . . . exception thrown:");
			errorPrint(e);
		}
	}

	/**
	 * Execute a simple SQL code, return result set.
	 * 
	 * @param query
	 *            SQL code
	 */
	public ResultSet ExeQuery(String query) {

		ResultSet myWishes;
		Statement s;
		// Beginning of Primary DB access section
		// ## BOOT DATABASE SECTION ##
		try {
			// check the connection. reconnect the db if connection not valid
			if (!conn.isValid(2))
				this.initDB();
			// ## INITIAL SQL SECTION ##
			// Create a statement to issue simple commands.
			s = conn.createStatement();
			// Call utility method to check if table exists.
			// Create the table if needed
			{
				System.out.println(query);
				myWishes = s.executeQuery(query);
				return myWishes;
			}
		} catch (Throwable e) {
			/*
			 * Catch all exceptions and pass them to the exception reporting
			 * method
			 */
			System.out.println(" . . . exception thrown:");
			errorPrint(e);
			return null;
		}
	}

	// ## DERBY EXCEPTION REPORTING CLASSES ##
	/***
	 * Exception reporting methods with special handling of SQLExceptions
	 ***/
	static void errorPrint(Throwable e) {
		if (e instanceof SQLException)
			SQLExceptionPrint((SQLException) e);
		else {
			System.out.println("A non SQL error occured.");
			e.printStackTrace();
		}
	} // END errorPrint

	// Iterates through a stack of SQLExceptions
	static void SQLExceptionPrint(SQLException sqle) {
		while (sqle != null) {
			System.out.println("\n---SQLException Caught---\n");
			System.out.println("SQLState:   " + (sqle).getSQLState());
			System.out.println("Severity: " + (sqle).getErrorCode());
			System.out.println("Message:  " + (sqle).getMessage());
			sqle.printStackTrace();
			sqle = sqle.getNextException();
		}
	} // END SQLExceptionPrint
}
